﻿CREATE PROCEDURE [dbo].[ASMS_SP_QueryServiceItemPagedList]
	@filter nvarchar(64),
	@categoryId uniqueidentifier,
	@businessId uniqueidentifier,
	@stationId uniqueidentifier,
	@status int = null,
	@start int,
	@limit int,
	@total int output
AS
SELECT T.* from (
	SELECT ROW_NUMBER() over(order by SI.Name) as _RowIndex, SI.BusinessId,
		SI.CategoryId,
		SI.Code,
		SI.CreatedTime,
		SI.Creator,
		case when SRS.Discount is not null then SRS.Discount
			when SRB.Discount is not null then SRB.Discount
			else SI.Discount end as Discount,
		SI.Id,
		SI.Letter,
		SI.Name,
		SI.Pinyin,
		case when SRS.Price is not null then SRS.Price
			when SRB.Price is not null then SRB.Price
			else SI.Price end as Price,
		case when SRS.SalesCommission is not null then SRS.SalesCommission
			when SRB.SalesCommission is not null then SRB.SalesCommission
			else SI.SalesCommission end as SalesCommission,
		case when SRS.SalesCommissionType is not null then SRS.SalesCommissionType
			when SRB.SalesCommissionType is not null then SRB.SalesCommissionType
			else SI.SalesCommissionType end as SalesCommissionType,
		SI.SortIndex,
		SI.StationId,
		SI.[Status],
		case when SRS.TaxRate is not null then SRS.TaxRate
			when SRB.TaxRate is not null then SRB.TaxRate
			else SI.TaxRate end as TaxRate,
		case when SRS.TechnicianCommission is not null then SRS.TechnicianCommission
			when SRB.TechnicianCommission is not null then SRB.TechnicianCommission
			else SI.TechnicianCommission end as TechnicianCommission,
		case when SRS.TechnicianCommissionType is not null then SRS.TechnicianCommissionType
			when SRB.TechnicianCommissionType is not null then SRB.TechnicianCommissionType
			else SI.TechnicianCommissionType end as TechnicianCommissionType,
		SC.Name as CategoryName
	from ASMS_ServiceItem as SI
		left join ASMS_ServiceCategory as SC on SC.Id = SI.CategoryId
		left join ASMS_ServiceItemRetain SRB on SRB.ServiceItemId = SI.Id and SRB.BusinessId = @businessId and SRB.StationId is null
		left join ASMS_ServiceItemRetain SRS on SRS.ServiceItemId = SI.Id and SRS.BusinessId = @businessId and SRS.StationId = @stationId
	where (@categoryId is null or SI.CategoryId = @categoryId) and (@status is null or SI.[Status] = @status) and (@filter is null or SI.Name like '%'+@filter +'%' )) as T
where T._RowIndex > @start and T._RowIndex <= @start + @limit

select @total = (select count(Id) from ASMS_ServiceItem where  (@categoryId is null or CategoryId = @categoryId) and (@status is null or [Status] = @status) and (@filter is null or Name like '%'+@filter +'%' ));
RETURN 0
